package com.yonyougov.devops.cruxcisupport.util.excell;

import com.yonyougov.devops.cruxcisupport.commitcontext.CommitContextDao;
import com.yonyougov.devops.cruxcisupport.commitcontext.CommitContextEntity;
import com.yonyougov.devops.cruxcisupport.committest.CommitTestDao;
import com.yonyougov.devops.cruxcisupport.committest.CommitTestEntity;
import com.yonyougov.devops.cruxcisupport.conver.commitcontext.CommitContextEntity2CruxCommitTestContentEntity;
import com.yonyougov.devops.cruxcisupport.enums.TestTypeEnum;
import com.yonyougov.devops.cruxcisupport.user.UserDao;
import com.yonyougov.devops.cruxcisupport.user.UserEntity;
import com.yonyougov.devops.cruxcisupport.util.JSONUtils;
import com.yonyougov.devops.cruxcisupport.util.excell.copysheet.CopySheetUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.RandomUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;

import java.io.*;
import java.math.BigInteger;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

@Slf4j
@Component
public class ExcellUtil {

    private final CommitTestDao commitTestDao;

    private final CommitContextDao commitContextDao;

    private final UserDao userDao;

    private final CommitContextEntity2CruxCommitTestContentEntity commitContextEntity2CruxCommitTestContentEntity;

    // 配置项
    private static final String[] templatePaths = {
            "excell_template/crux_commit_test_template.xls",
            "excell_template/crux_version_up_template.xls",
    };

    public ExcellUtil(CommitTestDao commitTestDao, CommitContextDao commitContextDao, UserDao userDao, CommitContextEntity2CruxCommitTestContentEntity commitContextEntity2CruxCommitTestContentEntity) {
        this.commitTestDao = commitTestDao;
        this.commitContextDao = commitContextDao;
        this.userDao = userDao;
        this.commitContextEntity2CruxCommitTestContentEntity = commitContextEntity2CruxCommitTestContentEntity;
    }

    // 平台提测模版
    public String cruxCommitTest(BigInteger commitTestId) throws Exception {
        log.info("[ ExcellUtil 提测: ] 开始生成excell");

        //---查数据开始
        //根据提测Id查询提测记录
        CommitTestEntity commitTestEntity = new CommitTestEntity();
        commitTestEntity.setId(commitTestId);
        commitTestEntity = commitTestDao.findBy(commitTestEntity);
        // 查询 多组 (应用,分支,版本,开发人员,备注)
        List<CommitContextEntity> commitContextEntities = commitContextDao.findAllBy(CommitContextEntity.builder().commitTestId(commitTestId).build());
        //查询提测人
        UserEntity submitUser = new UserEntity();
        submitUser.setId(commitTestEntity.getSubmitUserId());
        submitUser = userDao.findBy(submitUser);
        CruxCommitTestEntity cruxCommitTestEntity = new CruxCommitTestEntity();
        cruxCommitTestEntity.setProductName(commitTestEntity.getProductName());
        cruxCommitTestEntity.setCommitTestDate(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(LocalDateTime.now()));
        cruxCommitTestEntity.setCommitUserName((null == submitUser.getNickName() || "".equals(submitUser.getNickName())) ? submitUser.getUserName() : submitUser.getNickName());
        if (TestTypeEnum.FUNCTION.equals(commitTestEntity.getTestType())) {
            cruxCommitTestEntity.setNewAddContent(commitTestEntity.getTestContent());
        } else if (TestTypeEnum.BUG.equals(commitTestEntity.getTestType()) || TestTypeEnum.OPTIMIZE.equals(commitTestEntity.getTestType())) {
            cruxCommitTestEntity.setFixBugMessage(commitTestEntity.getTestContent());
        }
        cruxCommitTestEntity.setContent(commitContextEntity2CruxCommitTestContentEntity.batchCov(commitContextEntities));
        cruxCommitTestEntity.setScriptMessage("");
        cruxCommitTestEntity.setIsSelfTest("");
        cruxCommitTestEntity.setRemark(commitTestEntity.getRemark());
        //---查数据完毕
        log.info("[ ExcellUtil 提测: ] 数据 = {}", JSONUtils.toJSONString(cruxCommitTestEntity));

        Excell excell = new Excell()
                .setTemplate("excell_template/crux_commit_test_template.xls")
                .getSheet(0)
                .cellContentReplace(1, 1, "@@productName@@", cruxCommitTestEntity.getProductName())
                .cellContentReplace(2, 2, "@@productName@@", cruxCommitTestEntity.getProductName())
                .setCell(2, 4, cruxCommitTestEntity.getCommitTestDate())
                .setCell(3, 4, cruxCommitTestEntity.getCommitUserName())
                .setCell(5, 3, cruxCommitTestEntity.getNewAddContent())
                .setCell(7, 3, cruxCommitTestEntity.getFixBugMessage());

        // 动态生成 服务名称 分支名称 提测版本 开发人 备注
        int offset = 0;
        if (cruxCommitTestEntity.getContent().size() > 3) {
            for (int i = 4; i <= cruxCommitTestEntity.getContent().size(); i++) {
                excell.copyRowToInsert(10, 10);
                offset++;
            }
        }
        // 设置单元格内容
        Excell.Rect rect = new Excell.Rect(9, 3, 9 + 3 + offset, 3 + 4);
        List<String> list = new ArrayList<>();
        for (CruxCommitTestContentEntity content : cruxCommitTestEntity.getContent()) {
            list.add(content.getAppName());
            list.add(content.getBranchName());
            list.add(content.getVersion());
            list.add(content.getDeveloper());
            list.add(content.getRemark());
        }
        Iterator<String> iterator = list.iterator();
        while (rect.next() && iterator.hasNext()) {
            excell.setCell(rect.row, rect.col, iterator.next());
        }

        // 是否带脚本 是否自测 备注
        excell.setCell(13 + offset, 2, cruxCommitTestEntity.getIsSelfTest())
                .setCell(14 + offset, 2, cruxCommitTestEntity.getRemark());
        excell.deleteTemplateSheet();
        String currentDateTimeStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日-HH时mm分ss秒"));
        String absolutePath = excell.getFileAbsolutePath("提测申请表" + currentDateTimeStr);
        log.info("[ ExcellUtil 提测: ] excell文件生成成功,绝对路径 = {}", absolutePath);
        return absolutePath;
    }

    /**
     * 平台发版
     *
     * @param list java.util.List
     * @return excell文件的绝对路径
     */
    public String cruxRelease(List<CruxVersionUpEntity> list) throws Exception {
        log.info("[ ExcellUtil 发版: ] 数据 = {}", JSONUtils.toJSONString(list));

        Excell excell = new Excell();
        excell.setTemplate("excell_template/crux_version_up_template.xls");
        for (CruxVersionUpEntity sheetInfo : list) {
            // 填充excell
            int p1 = 7;
            int p2 = 11;
            int p3 = 16;
            int p4 = 19;
            int p5 = 22;
            excell.newSheet(sheetInfo.getSheetName())
                    .cellContentReplace(2, 2, "@@productName@@", sheetInfo.getProductName())
                    .cellContentReplace(3, 1, "@@versionName@@", sheetInfo.getVersion())
                    .cellContentReplace(4, 2, "@@appName@@", sheetInfo.getAppName())
                    .cellContentReplace(4, 2, "@@versionName@@", sheetInfo.getVersion())
                    .cellContentReplace(4, 2, "@@newAndPtimizeContent@@", sheetInfo.getNewAndPtimizeContent())
                    .setCell(25, 1, sheetInfo.getTestSuggest());
            //性能测试要求
            if (sheetInfo.getPerformTestAsks() != null) {
                for (String performTestAsk : sheetInfo.getPerformTestAsks()) {
                    excell.copyRowToInsert(p5, p5)
                            .setCell(p5, 3, performTestAsk);
                    p5++;
                }
            }
            //重点测试要求
            if (sheetInfo.getCruxVersionUpStressTestAskEntities() != null) {
                for (CruxVersionUpStressTestAskEntity cruxVersionUpStressTestAskEntity : sheetInfo.getCruxVersionUpStressTestAskEntities()) {
                    excell.copyRowToInsert(p4, p4)
                            .setCell(p4, 1, cruxVersionUpStressTestAskEntity.getId())
                            .setCell(p4, 2, cruxVersionUpStressTestAskEntity.getFunc())
                            .setCell(p4, 5, cruxVersionUpStressTestAskEntity.getTestStress());
                    p4++;
                }
            }

            //新增功能说明
            if (sheetInfo.getCruxVersionUpNewAddFuncRemarkEntities() != null) {
                for (CruxVersionUpNewAddFuncRemarkEntity cruxVersionUpNewAddFuncRemarkEntity : sheetInfo.getCruxVersionUpNewAddFuncRemarkEntities()) {
                    excell.copyRowToInsert(p3, p3)
                            .setCell(p3, 1, cruxVersionUpNewAddFuncRemarkEntity.getId())
                            .setCell(p3, 2, cruxVersionUpNewAddFuncRemarkEntity.getModuleName())
                            .setCell(p3, 3, cruxVersionUpNewAddFuncRemarkEntity.getRemark())
                            .setCell(p3, 5, cruxVersionUpNewAddFuncRemarkEntity.getFluFuncAndProcedure());
                    p3++;
                }
            }
            //Bug修改说明
            if (sheetInfo.getCruxVersionUpBugFixRemarkEntities() != null) {
                for (CruxVersionUpBugFixRemarkEntity cruxVersionUpBugFixRemarkEntity : sheetInfo.getCruxVersionUpBugFixRemarkEntities()) {
                    excell.copyRowToInsert(p2, p2)
                            .setCell(p2, 1, cruxVersionUpBugFixRemarkEntity.getBugId())
                            .setCell(p2, 2, cruxVersionUpBugFixRemarkEntity.getBugRemark())
                            .setCell(p2, 3, cruxVersionUpBugFixRemarkEntity.getFluFuncAndProcedure())
                            .setCell(p2, 5, cruxVersionUpBugFixRemarkEntity.getSolution());
                    p2++;
                }
            }

            //对服务器的其他要求
            if (sheetInfo.getCruxVersionUpAskEntities() != null) {
                for (CruxVersionUpAskEntity cruxVersionUpAskEntities : sheetInfo.getCruxVersionUpAskEntities()) {
                    excell.copyRowToInsert(p1, p1)
                            .setCell(p1, 1, cruxVersionUpAskEntities.getVersion())
                            .setCell(p1, 5, cruxVersionUpAskEntities.getRemark());
                    p1++;
                }
            }
        }
        excell.deleteTemplateSheet();
        log.info("[ ExcellUtil 发版: ] 开始生成excell");
        String currentDateTimeStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日-HH时mm分ss秒"));
        String absolutePath = excell.getFileAbsolutePath("版本构造说明" + currentDateTimeStr);
        log.info("[ ExcellUtil 发版: ] excell文件生成成功,绝对路径 = {}", absolutePath);
        return absolutePath;
    }

    private static final Map<String, File> templateFileMap;

    static {
        templateFileMap = new HashMap<>();
        String templatePath;
        for (String path : templatePaths) {
            File template = null;
            templatePath = path;
            try {
                InputStream inputStream = new ClassPathResource(templatePath).getInputStream();
                template = File.createTempFile("temp", "xls");
                FileUtils.copyInputStreamToFile(inputStream, template);
            } catch (IOException e) {
                e.printStackTrace();
            }
            if (template.exists()) {
                templateFileMap.put(templatePath, template);
            } else {
                log.debug("模版{}不存在", templatePath);
            }
        }
    }

    private static class Excell {
        HSSFSheet sheet;
        HSSFWorkbook wb;

        public Excell setTemplate(String templateName) {
            try {
                if (templateFileMap.containsKey(templateName)) {
                    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileMap.get(templateName)));
                    //读取excel模板
                    wb = new HSSFWorkbook(fs);
                    log.debug("{}模版读取成功,模版绝对路径={}:", templateName, templateFileMap.get(templateName).getAbsolutePath());
                } else {
                    log.debug("{}模版不存在", templateName);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return this;
        }

        public Excell newSheet(String sheetName) {
            //int templateIndex = wb.getSheetIndex("template");
            HSSFSheet template = wb.getSheet("template");

            HSSFSheet sheet = wb.createSheet("1");
            int sheetIndex = wb.getSheetIndex(sheet);
            wb.setSheetName(sheetIndex, sheetName);

            CopySheetUtil.copySheets(sheet, template, true);

            this.sheet = sheet;
            return this;
        }

        public Excell deleteTemplateSheet() {
            int templateIndex = wb.getSheetIndex("template");
            if (-1 != templateIndex) {
                wb.removeSheetAt(templateIndex);
            }
            return this;
        }

        public Excell getSheet(Integer index) {
            this.sheet = wb.getSheetAt(index);
            return this;
        }

        public Excell setCell(int row, int col, String value) {
            if (value == null) return this;
            row--;
            col--;
            if (sheet == null) return this;
            HSSFCell cell = sheet.getRow(row).getCell(col);
            cell.setCellValue(value);
            return this;
        }

        public Excell cellContentReplace(int row, int col, String oldKeyWord, String newKeyWord) {
            if(newKeyWord==null)return this;
            row--;
            col--;
            if (sheet == null) return this;
            HSSFCell cell = sheet.getRow(row).getCell(col);
            String stringCellValue = cell.getStringCellValue();
            String replace = stringCellValue.replace(oldKeyWord, newKeyWord);
            cell.setCellValue(replace);
            return this;
        }


        public Excell copyRowToInsert(int row01, int row02) {
            if (sheet == null) return this;
            row01--;
            row02--;
            if (row02 <= row01) {
                row01++;
            }
            sheet.shiftRows(row02, sheet.getLastRowNum(), 1, true, false);
            HSSFRow distRow = sheet.createRow(row02);
            copyRow(wb, sheet, sheet.getRow(row01), distRow, true);
            return this;
        }


        /**
         * 行复制功能
         *
         * @param fromRow org.apache.poi.hssf.usermodel.HSSFRow
         * @param toRow   org.apache.poi.hssf.usermodel.HSSFRow
         */
        public void copyRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow fromRow, HSSFRow toRow, boolean copyValueFlag) {
            toRow.setHeight(fromRow.getHeight());
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
                if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() +
                            (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress
                            .getFirstColumn(), cellRangeAddress.getLastColumn());
                    sheet.addMergedRegion(newCellRangeAddress);
                }
            }
            for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
                HSSFCell tmpCell = (HSSFCell) cellIt.next();
                // 可能会出错
                HSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
                copyCell(wb, tmpCell, newCell, copyValueFlag);
            }

        }

        /**
         * 复制单元格
         *
         * @param srcCell org.apache.poi.hssf.usermodel.HSSFCell
         * @param distCell org.apache.poi.hssf.usermodel.HSSFCell
         * @param copyValueFlag true则连同cell的内容一起复制
         */
        public static void copyCell(HSSFWorkbook wb, HSSFCell srcCell, HSSFCell distCell,
                                    boolean copyValueFlag) {
            HSSFCellStyle newstyle = wb.createCellStyle();
            newstyle.cloneStyleFrom(srcCell.getCellStyle());
            //样式
            distCell.setCellStyle(newstyle);
            //评论
            if (srcCell.getCellComment() != null) {
                distCell.setCellComment(srcCell.getCellComment());
            }
            // 不同数据类型处理
            int srcCellType = srcCell.getCellType();
            distCell.setCellType(srcCellType);
            if (copyValueFlag) {
                if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
                    if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
                        distCell.setCellValue(srcCell.getDateCellValue());
                    } else {
                        distCell.setCellValue(srcCell.getNumericCellValue());
                    }
                } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
                    distCell.setCellValue(srcCell.getRichStringCellValue());
                } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
                    distCell.setCellValue(srcCell.getBooleanCellValue());
                } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
                    distCell.setCellErrorValue(srcCell.getErrorCellValue());
                } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
                    distCell.setCellFormula(srcCell.getCellFormula());
                }
            }
        }


        public String getFileAbsolutePath(String fileName) throws Exception {
            if (wb == null) return null;
            File tempFile = null;
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            try {
                wb.write(bos);
                byte[] barray = bos.toByteArray();
                tempFile = File.createTempFile(fileName, ".xls");
                FileUtils.writeByteArrayToFile(tempFile, barray);
                // 重命名
                tempFile = com.yonyougov.devops.cruxcisupport.util.FileUtils.renameFile(tempFile, fileName + RandomUtils.nextInt(1000, 9999) + ".xls");
            } catch (IOException e) {
                e.printStackTrace();
            }
            if (Objects.requireNonNull(tempFile).exists()) {
                log.info("excell文件生成成功，文件路径={}", tempFile.getAbsolutePath());
            } else {
                log.info("excell文件生成失败");
            }
            return tempFile.getAbsolutePath();
        }

        static class Rect {
            public int maxCol;
            public int maxRow;
            public int minCol;
            public int minRow;
            public int col;
            public int row;
            Rect(int minRow,int minCol, int maxRow,int maxCol){
                this.maxCol = maxCol;
                this.maxRow = maxRow;
                this.minCol = minCol;
                this.minRow = minRow;
                this.col = minCol - 1;
                this.row = minRow;
            }
            public boolean next(){
                if((col + 1 - minCol)/(maxCol-minCol+1) == 1){
                    if((row + 1 - minRow)/(maxRow-minRow+1) == 1){
                        return false;
                    }
                    row++;
                    col = minCol - 1;
                }
                col++;
                return true;
            }
        }
    }

}